<%@page import="java.util.*, java.sql.*" %>

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Analytics - Application</title>
<script type="text/javascript" src = "application.js"></script>
</head>
<body> 

	<div id = "APPLICATIONINFO" style="display:none">
		<h1>Application of applicant:</h1>
		<div id = "APPLICATION">
			<div id = "GENERALINFO">
				<div id = "NAME"></div>
				<div id = "ADDRESS"></div>
				<div id = "COUNTRY"></div>
				<div id = "SPECIALIZATION"></div>
			</div>
		</div>
	</div>

<h1>List of applicants:</h1>

<%

	Connection conn = null;
	PreparedStatement stm = null;
	ResultSet result = null;
	
	try {
		Class.forName("org.postgresql.Driver");
		conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/Graduate_application_database", "postgres", "postgres");

		conn.setAutoCommit(false);
		if (request.getParameter("discipline") != null)
		{
			stm = conn.prepareStatement("SELECT applicants.name, applicants.address, countries_and_states.country_state, specializations.specialization, applicants.a_id " + 
									" FROM applicants, countries_and_states, specializations, degrees " + 
									" WHERE (applicants.country = countries_and_states.cs_id) AND (applicants.specialization = specializations.s_id) AND (degrees.applicant = applicants.a_id) AND (degrees.discipline = ?) ");
			stm.setInt(1, Integer.parseInt(request.getParameter("discipline")));
		}
		else if (request.getParameter("specialization") != null)
		{
			stm = conn.prepareStatement("SELECT applicants.name, applicants.address, countries_and_states.country_state, specializations.specialization, applicants.a_id " + 
					" FROM applicants, countries_and_states, specializations " + 
					" WHERE (applicants.country = countries_and_states.cs_id) AND (applicants.specialization = specializations.s_id) AND (applicants.specialization = ?)");
			stm.setInt(1, Integer.parseInt(request.getParameter("specialization")));			
		}
		else
		{
			stm = conn.prepareStatement("SELECT applicants.name, applicants.address, countries_and_states.country_state, specializations.specialization, applicants.a_id " + 
					" FROM applicants, countries_and_states, specializations " + 
					" WHERE (applicants.country = countries_and_states.cs_id) AND (applicants.specialization = specializations.s_id)");
			
		}
		result = stm.executeQuery();
		
		conn.commit();
		conn.setAutoCommit(true);
%>
		<table>
		<tr>
			<td><b>Name</b></td>
			<td><b>Address</b></td>
			<td><b>Citizenship</b></td>
			<td><b>Specialization</b></td>
			<td style = "display:none;" ><b>Applicant ID</b></td>
		</tr>


<%
		int i = 0;
		while (result.next())
		{%>
			<tr>
				<td id =<%= "n" + Integer.toString(i) %> ><%= result.getString(1) %></td>
				<td id =<%= "a" + Integer.toString(i) %> ><%= result.getString(2) %></td>
				<td id =<%= "c" + Integer.toString(i) %> ><%= result.getString(3) %></td>
				<td id =<%= "s" + Integer.toString(i) %> ><%= result.getString(4) %></td>
				<td id =<%= "i" + Integer.toString(i) %> style = "display:none;" ><%= result.getString(5) %></td>
				<td id = <%= "rs" + Integer.toString(i) %>>
					<input type = "button" id=<%= "b" + Integer.toString(i) %> value = "Show Application" onclick = "showApp(this.id);"/>
				</td>
				<td id = <%= "rh" + Integer.toString(i) %> style = "display:none;" >
					<input type = "button" id=<%= "h" + Integer.toString(i) %> value = "Hide Application" onclick = "hideApp(this.id);" style="width:117px"/>
				</td>
			</tr>
			
<%	i++;	
}%>
		</table>
<%
		result.close();
		stm.close();
		conn.close();
	}
	catch (SQLException e)
	{
		throw new RuntimeException(e);
	}
    finally {
        if (result != null) {
            try {
                result.close();
            } 
            catch (SQLException e) { }
            result = null;
        }
        if (stm != null) {
            try {
                stm.close();
            } 
            catch (SQLException e) { }
            stm = null;
        }
        if (conn != null) {
            try {
                conn.close();
            } 
            catch (SQLException e) { }
            conn = null;
        }
    }
%>	

</body>
</html>